﻿using System.IO;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Transactions;
namespace WinnerCMS.Common
{
    #region 委托
    /// <summary>
    /// 执行命令
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="dbCommand"></param>
    /// <returns></returns>
    internal delegate T ExecuteHandle<T>(DbCommand dbCommand);

    #endregion

    /// <summary>
    /// 数据访问抽象基础类
    /// </summary>
    public static class SQLBaseV2
    {
        /// <summary>
        /// 通过企业库读取配置信息，获得一个Database对象
        /// </summary>
        private static readonly Database Database = DatabaseFactory.CreateDatabase();


        #region 私有方法

        /// <summary>
        /// 处理下调用ExecuteScalar返回的object值
        /// </summary>
        /// <param name="obj">object值</param>
        /// <returns>处理后的object值</returns>
        private static object ExecuteScalarResult(object obj)
        {
            if ((Equals(obj, null)) || (Equals(obj, DBNull.Value)))
            {
                return null;
            }
            else
            {
                return obj;
            }
        }

        /// <summary>
        /// 执行命令，基方法
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="executeBySql"></param>
        /// <param name="sqlString"></param>
        /// <param name="commandType"></param>
        /// <param name="parameterValue"></param>
        /// <remarks>执行sql 脚本，但可设置是否为存储过程</remarks>
        /// <returns></returns>
        private static T Execute<T>(ExecuteHandle<T> executeBySql, string sqlString, CommandType commandType, DbParameter[] parameterValue)
        {
            DbCommand dbCommand = Database.GetSqlStringCommand(sqlString);
            dbCommand.CommandType = commandType;
            if (parameterValue != null)
                dbCommand.Parameters.AddRange(parameterValue);


            T t = executeBySql.Invoke(dbCommand);

            if (!(t is IDataReader))
                dbCommand.Parameters.Clear();

            return t;
        }

        #endregion

        #region ExecuteNonQuery
        public static int ExecuteNonQuery(string strSql)
        {
            return ExecuteNonQuery(strSql, CommandType.Text, null);
        }
        public static int ExecuteNonQuery(List<string> sqllist)
        {
            using (TransactionScope scope = new TransactionScope())
            {
                foreach (string sql in sqllist)
                {
                    ExecuteNonQuery(sql, CommandType.Text, null);
                } 
                scope.Complete();
            } return 1;
        }
        public static int ExecuteNonQuery(string strSql, DbParameter[] arrParameter)
        {
            return ExecuteNonQuery(strSql, CommandType.Text, arrParameter);
        }
        public static int ExecuteNonQuery(string strSql, CommandType ct, DbParameter[] arrParameter)
        {
            return Execute(Database.ExecuteNonQuery, strSql, ct, arrParameter);
        }

        #endregion

        #region ExecuteScalar
        public static object ExecuteScalar(string strSql)
        {
            return ExecuteScalar(strSql, CommandType.Text, null);
        }
        public static object ExecuteScalar(string strSql, CommandType ct)
        {
            return ExecuteScalar(strSql, ct, null);
        }
        public static object ExecuteScalar(string strSql, DbParameter[] arrParameter)
        {
            return ExecuteScalar(strSql, CommandType.Text, arrParameter);
        }

        /// <summary>
        /// 执行有参数的存储过程，并返回查询所返回的结果集中第一行的第一列。所有其他的列和行将被忽略。
        /// </summary>
        /// <returns>返回结果集中第一行的第一列。</returns>
        public static object ExecuteScalar(string strSql, CommandType ct, DbParameter[] arrParameter)
        {
            object obj = Execute<object>(Database.ExecuteScalar, strSql, ct, arrParameter);

            return ExecuteScalarResult(obj);
        }

        #endregion

        #region ExecuteReader

        public static IDataReader ExecuteReader(string strSql)
        {
            return ExecuteReader(strSql, CommandType.Text, null);
        }
        public static IDataReader ExecuteReader(string strSql, CommandType ct)
        {
            return ExecuteReader(strSql, ct, null);
        }
        public static IDataReader ExecuteReader(string strSql, DbParameter[] arrParameter)
        {
            return ExecuteReader(strSql, CommandType.Text, arrParameter);
        }
        public static IDataReader ExecuteReader(string strSql, CommandType ct, DbParameter[] arrParameter)
        {
            IDataReader dr = Execute(Database.ExecuteReader, strSql, ct, arrParameter);
            return dr;

        }
        #endregion

        #region ExecuteDataSet
        public static DataSet ExecuteDataSet(string strSql)
        {
            return ExecuteDataSet(strSql, CommandType.Text, null);
        }
        public static DataSet ExecuteDataSet(string strSql, CommandType ct)
        {
            return ExecuteDataSet(strSql, ct, null);
        }
        public static DataSet ExecuteDataSet(string strSql, DbParameter[] arrParameter)
        {
            return ExecuteDataSet(strSql, CommandType.Text, arrParameter);
        }
        public static DataSet ExecuteDataSet(string strSql, CommandType ct, DbParameter[] arrParameter)
        {
            return Execute(Database.ExecuteDataSet, strSql, ct, arrParameter);
        }
        #endregion

        #region ReturnDataRow
        public static DataRow ReturnDataRow(string strSql)
        {
            return ReturnDataRow(strSql, CommandType.Text, null);
        }
        public static DataRow ReturnDataRow(string strSql, CommandType ct)
        {
            return ReturnDataRow(strSql, ct, null);
        }
        public static DataRow ReturnDataRow(string strSql, DbParameter[] arrParameter)
        {
            return ReturnDataRow(strSql, CommandType.Text, arrParameter);
        }
        public static DataRow ReturnDataRow(string strSql, CommandType ct, DbParameter[] arrParameter)
        {
            DataSet ds = ExecuteDataSet(strSql, ct, arrParameter);
            if (ds.Tables[0].Rows.Count == 0)
            {
                return null;
            }
            else
            {
                return ds.Tables[0].Rows[0];
            }
        }
        #endregion

        #region  Sql文本转换为批量执行Sql脚本
        /// <summary>
        /// Sql文本转换为批量执行Sql脚本
        /// </summary>
        /// <param name="sqlText"></param>
        /// <returns></returns>
        static public List<string> SqlText2SqlList(string sqlText)
        {
            List<string> sqlList = new List<string>();
            string commandText = "";
            foreach (string varLine in Regex.Split(sqlText, "\r\n"))
            {
                if (varLine.Trim().Length == 0)
                {
                    continue;
                }
                if (varLine.Trim() != "GO")
                {
                    commandText += varLine.Trim();
                    commandText += "\r\n";
                }
                else
                {
                    sqlList.Add(commandText);
                    commandText = "";
                }
            }
            return sqlList;
        }
        /// <summary>
        /// Sql文本转换为批量执行Sql脚本
        /// </summary>
        /// <param name="sr"></param>
        /// <returns></returns>
        static public List<string> SqlText2SqlList(TextReader sr)
        {
            List<string> sqlList = new List<string>();
            string commandText = "";
            while (sr.Peek() > -1)
            {
                string varLine = sr.ReadLine();
                if (varLine.Length == 0)
                {
                    continue;
                }
                if (varLine != "GO")
                {
                    commandText += varLine;

                    commandText += "\r\n";
                }
                else
                {
                    sqlList.Add(commandText);
                    commandText = "";
                }
            }
            sr.Dispose();
            return sqlList;
        }
        #endregion

    }
}
